package jdbc420.demo;

import jdbc420.entity.User;
import jdbc420.utils.DruidUtil;
import jdk.nashorn.internal.ir.CallNode;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author:SA
 * @DATA:DATA
 *
 * 测试数据路连接池的使用
 */


public class TestUtil {
    public static void main(String[] args) {
         test06();

    }
        //使用数据库连接池 进行数据的查询
        public static void test01(){
            //获取连接
            Connection connection = DruidUtil.getConnection();
            PreparedStatement preparedStatement = null;
            ResultSet resultSet = null;
            String sql = "select *  from dept";
            try {
                preparedStatement = connection.prepareStatement(sql);
                resultSet = preparedStatement.executeQuery();
                ArrayList<User> users = new ArrayList<>();
                while (resultSet.next()) {
                    int dept_no = resultSet.getInt("dept_no");
                    String dept_name = resultSet.getString("dept_name");
                    String address = resultSet.getString("address");

                    //User user = new User();
                    //user.setId(id);
                    //user.setPassword(pwd);
                    //user.setUname(uname);

                    //User user = User.builder().id(id).password(pwd).uname(uname).build();
                    User user = new User(dept_no,dept_name,address);
                    users.add(user);
                    //System.out.println("user = " + user);
                }
                users.forEach(user -> System.out.println("user = " + user));

            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } finally {
                DruidUtil.closeAll(resultSet, preparedStatement, connection);
            }

        }

        //DButils工具类的使用
        public static void test02(){
        //创建queryRunner  操作数据库对象
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        //查询数据库中的所有数据 dept
            String sql = "select * from dept";//集合 泛型
            try {
                //为了保证数据封装的准确性  要让数据库中查询的字段名称和实体类中的属性的名字保持一致 否则封装失败  反射
                List<User> users = queryRunner.query(sql, new BeanListHandler<>(User.class));
                //遍历结果集
                users.forEach(user -> System.out.println("user = " + user));
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }

    public static void test03(){//有条件的查询
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "select * from dept where dept_no = ?";
        try {
            //只是查询一条数据 所以用BeanHandler就可以
            User query = queryRunner.query(sql, new BeanHandler<>(User.class), 99);
            System.out.println("query = " + query);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }

    public static void test04(){
        //添加数据
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql ="insert into dept (dept_name,address)values(?,?)";

        try {
            int line = queryRunner.update(sql, "总部", "杭州");
            System.out.println("line = " + line);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }
    public static void test05(){//更新数据
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = " update dept set address= ? where dept_no = ?";
        try {
            int line = queryRunner.update(sql, "天津", 100);
            System.out.println("line = " + line);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }


    }
    public static void test06(){//删除数据
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql ="delete from dept where dept_no = ?";
        try {
            int line = queryRunner.update(sql, 100);
            System.out.println("line = " + line);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

    }

}
